package dLogistic.domain.model.Klient;

import dLogistic.domain.model.Magazyn.MagazynAggr;
import dLogistic.domain.model.Magazyn.MagazynRepository;
import dLogistic.domain.model.sharedValueObjects.Adres;
import dLogistic.domain.service.GeolokalizacjaService;
import dLogistic.infrastructure.persistence.hibernate.HibernateRepository;
import dLogistic.infrastructure.persistence.hibernate.HibernateUtil;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Przemek on 2014-10-24.
 */
public class KlientRepository extends HibernateRepository {
    private static SessionFactory factory;

    public void zapisz(KlientAggr klient) {
        factory = HibernateUtil.getSessionFactory();
        Session session = factory.getCurrentSession();
        Transaction transaction = session.beginTransaction();
        session.saveOrUpdate(klient);
        transaction.commit();
    }

    public KlientAggr znajdz(String uuid){
        factory = HibernateUtil.getSessionFactory();
        Session session = factory.getCurrentSession();

        Transaction transaction = session.beginTransaction();
        String sql = "select * from Klient where uuid = :uuid";
        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity(KlientAggr.class);
        query.setParameter("uuid", uuid);
        List results = query.list();
        transaction.commit();
        if (!results.isEmpty()){
            return (KlientAggr) results.get(0);
        }
        else {
            return null;
        }
    }

    public JSONArray zwrocListAll(){
        factory = HibernateUtil.getSessionFactory();
        Session session = factory.getCurrentSession();
        Transaction transaction = session.beginTransaction();

        String sql = "select * from Klient";
        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity(KlientAggr.class);

        List results = query.list();
        transaction.rollback();
        JSONArray ja = new JSONArray();
        for (Integer i = 0; i < results.size(); i++) {
            KlientAggr klientAggr = (KlientAggr) results.get(i);
            JSONObject obj = new JSONObject();
            obj.put("uuid", klientAggr.uuid());
            obj.put("imie", klientAggr.imie());
            obj.put("naziwsko", klientAggr.nazwisko());
            obj.put("telefon", klientAggr.telefon());
            obj.put("email", klientAggr.email());
            ja.add(obj);
        }
        return ja;
    }

    public MagazynAggr najblizszyMagazyn(KlientAggr klientAggr){
        GeolokalizacjaService geo = new GeolokalizacjaService();
        ArrayList geoLok = geo.ZwrocWspolrzedneXY(klientAggr.uuid());

        factory = HibernateUtil.getSessionFactory();
        Session session = factory.getCurrentSession();
        Transaction transaction = session.beginTransaction();



/*
        String sql = "select a.*, square(a.latitude - :x) + square(a.longitude - :y) from Adres a " +
                " where a.longitude is not null and a.latitude is not null and a.typ = :typ " +
                " order by a.uuid asc";
*/
        String sql = "select square(a.latitude - :x) + square(a.longitude - :y), a.*" +
                "from Adres a " +
                "where a.longitude is not null and a.latitude is not null " +
                "and a.typ = :typ order by 1 asc";
        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity(Adres.class);

        query.setParameter("x", geoLok.get(0).toString());
        query.setParameter("y", geoLok.get(1).toString());
        query.setParameter("typ", "1");

        List results = query.list();
        transaction.rollback();
        if (!results.isEmpty()){
            Adres adres = (Adres) results.get(0);
            MagazynRepository magazynRepository = new MagazynRepository();
            MagazynAggr magazynAggr = magazynRepository.znajdz(adres.uuid_parent());
            return magazynAggr;
        }
        else {
            return null;
        }
    }

}
